﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using Google.GData.Documents;
using DataType;

namespace Utilities
{
    public class DbOperations
    {
        private DbManager m_db;

        public DbOperations(DbManager db)
        { m_db = db; }

        public void DoSaveCredentials(Credentials cred)
        {
            try
            {
                using (DbManager.DBConnection con = m_db.CreateConnection())
                {
                    string command = "DELETE FROM Credentials";
                    con.ExecuteNonQuery(command, null);

                    if (cred.m_remember == true)
                    {
                        command = "INSERT INTO Credentials (Email, [Password]) VALUES (@Email, @Password)";

                        OleDbParameter[] param = new OleDbParameter[2];
                        param[0] = new OleDbParameter("@Email", cred.m_Cred.Username);
                        param[1] = new OleDbParameter("@Password", cred.m_password);

                        con.ExecuteNonQuery(command, param);
                    }
                }
            }
            catch (Exception e)
            {
                TraceWrapper.LogException("DoSaveCredentials error: " + e.Message, e);
            }
        }

        public void DoLoadCredentials(Credentials cred)
        {
            try
            {
                using (DbManager.DBConnection con = m_db.CreateConnection())
                {
                    string command = "SELECT Email, [Password] FROM Credentials";

                    OleDbDataReader reader = con.ExecuteQuery(command, null);
                    reader.Read();
                    cred.m_Cred.Username = (string)reader["Email"];
                    cred.m_password = (string)reader["Password"];
                    cred.m_remember = true;
                }
            }
            catch (Exception e)
            {
                TraceWrapper.LogException("DoLoadCredentials error: " + e.Message, e);
            }
        }

        public void DoLookupRemoteCopy(DocumentInfo info)
        {
            try
            {
                using (DbManager.DBConnection con = m_db.CreateConnection())
                {
                    string command = "SELECT ID, GoogleDocID, LocalFileName FROM FileLinks WHERE LocalFileName = @ID";

                    OleDbParameter[] param = new OleDbParameter[1];
                    param[0] = new OleDbParameter("@ID", info.m_FullName);

                    OleDbDataReader reader = con.ExecuteQuery(command, param);
                    reader.Read();
                    info.m_RemoteId = (string)reader["GoogleDocID"];
                }
            }
            catch (Exception e)
            {
                TraceWrapper.LogException("DoLookupRemoteCopy error: " + e.Message, e);
            }
        }

        public void DoLookupLocalCopy(DocumentInfo info)
        {
            try
            {
                using (DbManager.DBConnection con = m_db.CreateConnection())
                {
                    //at first check do we previously download this document
                    string command = "SELECT ID, GoogleDocID, LocalFileName FROM FileLinks WHERE GoogleDocID = @ID";

                    OleDbParameter[] param = new OleDbParameter[1];
                    param[0] = new OleDbParameter("@ID", info.m_DocEntry.Id.AbsoluteUri);

                    OleDbDataReader reader = con.ExecuteQuery(command, param);
                    reader.Read();
                    info.m_FullName = (string)reader["LocalFileName"];

                    //now check does this document still exist at the same location,
                    //if it doesn't exist we have to remove record from DB because it's the case
                    //of new downloaded document
                    FileInfo fs = new FileInfo(info.m_FullName);
                    if (!fs.Exists)
                    {
                        string commandDel = "DELETE FROM FileLinks WHERE LocalFileName = @LOCAL";
                        OleDbParameter[] paramDel = new OleDbParameter[1];
                        paramDel[0] = new OleDbParameter("@LOCAL", info.m_FullName);
                        con.ExecuteNonQuery(commandDel, paramDel);
                        info.m_FullName = null;
                    }
                }
            }
            catch (Exception e)
            {
                TraceWrapper.LogException("DoLookupLocalCopy error: " + e.Message, e);
            }
        }

        public void DoRegisterLocalCopy(DocumentInfo info)
        {
            try
            {
                using (DbManager.DBConnection con = m_db.CreateConnection())
                {
                    // it can't exist two rows with the same path to the local path
                    string commandDel = "DELETE FROM FileLinks WHERE LocalFileName = @LOCAL";
                    OleDbParameter[] paramDel = new OleDbParameter[1];
                    paramDel[0] = new OleDbParameter("@LOCAL", info.m_FullName);
                    con.ExecuteNonQuery(commandDel, paramDel);

                    string command = "INSERT INTO FileLinks (GoogleDocID, LocalFileName) VALUES (@ID, @LOCAL)";

                    OleDbParameter[] param = new OleDbParameter[2];
                    param[0] = new OleDbParameter("@ID", info.m_DocEntry.Id.AbsoluteUri);
                    param[1] = new OleDbParameter("@LOCAL", info.m_FullName);

                    con.ExecuteNonQuery(command, param);
                }
            }
            catch (Exception e)
            {
                TraceWrapper.LogException("DoRegisterLocalCopy error: " + e.Message, e);
            }
        }

        public string LookForContact()
        {
            //bool result = true;
            //ZohoTypes.ZohoContact contact = null;
            try
            {
                using (DbManager.DBConnection con = m_db.CreateConnection())
                {
                    string command = "SELECT Email FROM RegisteredContact";

                    OleDbDataReader reader = con.ExecuteQuery(command, null);
                    reader.Read();

                    return (string)reader["Email"]; ;
                }

            }
            catch (Exception e)
            {
                TraceWrapper.LogException("RegisteredContactBlocked error: " + e.Message, e);
                throw e;
            }
        }

        public void AddRegisterContact(string fname, string lname, string email)
        {
            try
            {
                using (DbManager.DBConnection con = m_db.CreateConnection())
                {
                    // it can't exist two rows with the same path to the local path
                    string commandDel = "DELETE FROM RegisteredContact";
                    //OleDbParameter[] paramArr = new OleDbParameter[0];
                    //paramDel[0] = new OleDbParameter("@LOCAL", info.m_Path);
                    con.ExecuteNonQuery(commandDel, null);

                    string command = "INSERT INTO RegisteredContact " +
                                    "(FirstName, LastName, Email) VALUES (@FirstName, @LastName, @Email)";

                    OleDbParameter[] paramArr = new OleDbParameter[3];
                    paramArr[0] = new OleDbParameter("@FirstName", fname);
                    paramArr[1] = new OleDbParameter("@LastName", lname);
                    paramArr[2] = new OleDbParameter("@Email", email);

                    con.ExecuteNonQuery(command, paramArr);
                }
            }
            catch (Exception e)
            {
                TraceWrapper.LogException("AddRegisterContact error: " + e.Message, e);
            }
        }
    }
}
